﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Globalization;

namespace BSC_SERVICES.Code
{
    public class ImportData
    {
        #region khai bao bien
        string _connectstring = ConfigurationManager.AppSettings["ConnectString"].ToString();
        OracleCommand _cmd = new OracleCommand();
        Code.Connect_DataBase _db = new Connect_DataBase();
        Code.CL_Ultility _cl = new CL_Ultility();
        Code.Common _cm = new Common();
        OracleConnection _con = new OracleConnection();
        #endregion


        private void OpenConnect()
        {
            if (_con.State != ConnectionState.Open)
            {
                _con.ConnectionString = _connectstring;
                _con.Open();
            }
        }

        private void CloseConnect()
        {
            _con.Close();
        }

        //thuc thi transaction import danh sach
        public DataTable Import_TableData(string _formName, DataTable _tbl, string userID, string menu_Code, string duyet, string _function)
        {
            //bang du lieu tra ve
            DataTable _tblReturn = new DataTable("_tblReturn");
            _tblReturn = _tbl.Copy();
            //_tblReturn.Columns.Add("MAKH");
            //open connection
            OpenConnect();
            _cmd = _con.CreateCommand();
            OracleTransaction _trans;

            // Start a local transaction
            _trans = _con.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            _cmd.Transaction = _trans;

            try
            {
                string id = "";
                string key = "";
                //lấy key của bảng
                key = PrimaryKey(_formName);

                //add key trong bảng trả vể
                _tblReturn.Columns.Add(key);

                //inserrt vao bang du lieu that
                if (_tbl.Rows.Count == 1)
                {
                    id = AddNewRowReturnID(_formName, _tbl, key);
                    _tblReturn.Rows[0][key] = id;
                }
                else
                {
                    if (_tbl.Rows.Count > 1)
                    {
                        //duyệt từng bản ghi
                        DataTable noname = new DataTable("noname");
                        /*foreach (DataColumn col in _tbl.Columns)
                        {
                            noname.Columns.Add(col.ColumnName, col.DataType);
                        }*/
                        noname = _tbl.Clone();

                        for (int i = 0; i < _tbl.Rows.Count; i++)
                        {
                            //xoá bản ghi cũ
                            noname.Clear();
                            //add row data moi
                            noname.Rows.Add(_tbl.Rows[i].ItemArray);
                            //them moi 1 ban ghi vao bang
                            id = AddNewRowReturnID(_formName, noname, key);
                            //add id vao bang tra ve
                            _tblReturn.Rows[i][key] = id;
                            //luu vet truy van
                            _db.WritetoTLLOG(userID,
                                         "",
                                         menu_Code,
                                         _cm.Current_DATE("dd/MM/yyyy"),
                                         "",
                                         _cm.Current_DATE("hh:mm:ss"),
                                         "",
                                         duyet + "",
                                         _function,
                                         id,
                                         _cl.DataTableToString(noname));
                        }
                    }
                }

                _trans.Commit();
                Console.WriteLine("All records are written to database.");
            }
            catch (Exception ex)
            {

                _trans.Rollback();
                _cl.UPDATE_LOG_FILE("ERR", userID, "Import_TableData", "Import_TableData", ex.Message);
                Console.WriteLine(ex.ToString());
                Console.WriteLine("Neither record was written to database.");
                CloseConnect();
                return null;
            }
            finally
            {
                CloseConnect();
            }
            return _tblReturn;
        }

        public void Update_Table_Last_Change(string _formName, string _userID, DataTable _data)
        {
             OpenConnect();
            _cmd = _con.CreateCommand();
            OracleTransaction _trans;

            // Start a local transaction
            _trans = _con.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            _cmd.Transaction = _trans;

            try
            {
                string id = "";
                string key = "";
                //lấy key của bảng
                key = PrimaryKey(_formName);

                if (_data.Rows.Count > 1)
                {

                    for (int i = 0; i < _data.Rows.Count; i++)
                    {
                        id = _data.Rows[i][key].ToString();
                        _db.Excute_DB("update " + _formName + " set last_changeid = '" + _userID + "',"
                                     + " last_change = TO_DATE('" + _cm.Convert_String2DateTime(DateTime.Now.ToString(), "dd/mm/yyyy hh:pp:ss") + "','dd/mm/yyyy hh24:mi:ss')"
                                     + " where " + key + " = '" + id + "'");
                    }
                }

                _trans.Commit();
                Console.WriteLine("All records are written to database.");
            }
            catch (Exception ex)
            {

                _trans.Rollback();
                _cl.UPDATE_LOG_FILE("ERR", _userID, "Update_Table_Last_Change", "Update_Table_Last_Change", ex.Message);
                Console.WriteLine(ex.ToString());
                Console.WriteLine("Neither record was written to database.");
                CloseConnect();
            }
            finally
            {
                CloseConnect();
            }
        }

        public string AddNewRowReturnID(string _fldname, DataTable _tableValue, string primarykey)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<object> _values = new List<object>();
            List<string> _datatype = new List<string>();

            // tạo câu lệnh insert dựa theo bảng _tableValue
            //string sql = "Insert into " + _fldname + " ("+this.PrimaryKey(_fldname)+",";
            string sql = "Insert into " + _fldname + " (";
            foreach (DataColumn column in _tableValue.Columns)
            {
                sql += column.ColumnName.Trim() + ",";
                _para.Add(column.ColumnName.Trim());
                DataRow aa = _tableValue.Rows[0];
                _values.Add(_tableValue.Rows[0][column].ToString());
                if (column.DataType == typeof(DateTime))
                    _datatype.Add("D");
                else
                    _datatype.Add("T");
            }
            //sql = sql.Substring(0, sql.Length - 1) + ") values(scq_" + _fldname + ".NEXTVAL,";
            sql = sql.Substring(0, sql.Length - 1) + ") values(";
            foreach (DataColumn column in _tableValue.Columns)
            {
                if (column.DataType == typeof(DateTime))
                    sql += "TO_DATE(:" + column.ColumnName.Trim() + ",'dd/MM/yyyy'),";
                else
                    sql += ":" + column.ColumnName.Trim() + ",";
            }
            if (primarykey == null)
                sql = sql.Substring(0, sql.Length - 1) + ") returning autoid into :myOutputParameter";
            else
                sql = sql.Substring(0, sql.Length - 1) + ") returning " + primarykey + " into :myOutputParameter";
            // chạy lệnh insert, thành công trả lại True, lỗi trả lại messeage
            //WritetoTLLOG(_maker_ID, "", _tltxcd,_busdate,_txdesc,_txstatus,_msgacct,_chktime);
            return RunSql_ByParameter_ReturnID(sql, _para, _values, _datatype, "Add");
        }

        private string PrimaryKey(string _fldname)
        {
            //"select * from bsc_primary_key where table_name='" + _tablename + "' ";
            //OpenConnect();
            string result = "";
            _cmd.CommandText = "select primarykey_name from bsc_primary_key where table_name ='" + _fldname + "'";
            result = (String)_cmd.ExecuteScalar();
            return result;
        }

        private string RunSql_ByParameter_ReturnID(string sql, List<string> _paras, List<object> _values, List<string> _datatype, string _action)
        {
            //OpenConnect();
            //_cmd = new OracleCommand(sql);
            _cmd.CommandText = sql;
            _cmd.Parameters.Clear();
            for (int i = 0; i < _paras.Count; i++)
            {
                switch (_datatype[i])
                {
                    case "B":
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = _values[i];
                        break;
                    case "D":
                        if (_values[i].ToString().Length == 0)
                            _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i])).Value = OracleDateTime.Null;
                        else
                        {
                            _cmd.Parameters.Add(_paras[i], OracleType.VarChar).Value = _cm.Convert_String2DateTime(_values[i].ToString(), "dd/MM/yyyy");
                        }
                        break;
                    default:
                        _cmd.Parameters.Add(_paras[i], _cl.RowType(_datatype[i]), 2000).Value = _values[i];
                        break;
                }
            }
            _cmd.Parameters.Add("myOutputParameter", OracleType.Int16).Direction = ParameterDirection.ReturnValue;
            _cmd.Connection = _con;
            _cmd.ExecuteNonQuery();

            return _cmd.Parameters["myOutputParameter"].Value.ToString();
        }

        //them chu ky theo danh sach cho kh
        public bool Import_Signals(DataTable _tbl, string userID, string menu_Code, string duyet, string _function)
        {
            //open connection
            OpenConnect();
            _cmd = _con.CreateCommand();
            OracleTransaction _trans;

            // Start a local transaction
            _trans = _con.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            _cmd.Transaction = _trans;

            try
            {
                //duyệt từng bản ghi
                DataTable noname = new DataTable("noname");
                noname = _tbl.Clone();

                for (int i = 0; i < _tbl.Rows.Count; i++)
                {
                    //xoá bản ghi cũ
                    noname.Clear();
                    //add row data moi
                    noname.Rows.Add(_tbl.Rows[i].ItemArray);
                    //add sql query
                    _cmd.CommandText = "update BSC_TBL_KHACHHANG set " + noname.Columns[1].ColumnName + "=:" + noname.Columns[1].ColumnName +
                                       " where " + noname.Columns[0].ColumnName + "=:" + noname.Columns[0].ColumnName;
                    _cmd.Parameters.Clear(); //xóa param cũ
                    _cmd.Parameters.Add(":" + noname.Columns[1].ColumnName, OracleType.VarChar).Value = noname.Rows[0][1];
                    _cmd.Parameters.Add(":" + noname.Columns[0].ColumnName, OracleType.VarChar).Value = noname.Rows[0][0];
                    _cmd.ExecuteNonQuery();

                    //luu vet truy van
                    _db.WritetoTLLOG(userID,
                                 "",
                                 menu_Code,
                                 _cm.Current_DATE("dd/MM/yyyy"),
                                 "",
                                 _cm.Current_DATE("hh:mm:ss"),
                                 "",
                                 duyet + "",
                                 _function,
                                 noname.Rows[0][0].ToString(),
                                 _cl.DataTableToString(noname));
                }
                _trans.Commit();
                Console.WriteLine("All records are written to database.");
            }
            catch (Exception ex)
            {

                _trans.Rollback();
                _cl.UPDATE_LOG_FILE("ERR", userID, "Import_Signals", "Import_Signals", ex.Message);
                Console.WriteLine(ex.ToString());
                Console.WriteLine("Neither record was written to database.");
                CloseConnect();
                return false;
            }
            finally
            {
                CloseConnect();
            }
            return true;
        }
    }
}